package com.zaoke.web.dao.order.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.zaoke.web.bo.order.Order;
import com.zaoke.web.bo.order.OrderFood;
import com.zaoke.web.dao.JdbcSupport;
import com.zaoke.web.dao.order.OrderDao;
import com.zaoke.web.utils.DoubleFormat;

public class OrderDaoImpl extends JdbcSupport implements OrderDao {
	
	private Log logger = LogFactory.getLog(getClass());

	private static final long DAY = 24*60*60*1000;
	
	private static final long EIGHT = 8*60*60*1000;
	
	@Override
	public List<Order> getOrderByQuery(Order order, Date dateF, Date dateT) {
		
		if(order==null)
			return new ArrayList<Order>();
		
		String sql = "select * from zaoke.`order` where 1=1";
		
		Long userid = order.getUserid();
		Date booktime = order.getBooktime();
		Date consumetime = order.getConsumetime();
		Date ordertime = order.getOrdertime();
		Integer payway = order.getPayway();
		Double price = order.getPrice();
		Long zoneid = order.getZoneid();
		Integer state = order.getState();
		List<Object> params = new LinkedList<Object>();
		
		if(dateF != null && dateT != null){
			sql += " and book_time between ? and ? ";
			params.add(dateF);
			params.add(dateT);
		}
		
		if(userid!=null && userid > 0)
		{
			sql += " and user_id=? ";
			params.add(userid);
		}
		if(booktime!=null)
		{
			booktime = new Date(booktime.getTime()-EIGHT);
			Date nextDay = new Date(booktime.getTime()+DAY);
			sql += " and book_time between ? and ? ";
			params.add(booktime);
			params.add(nextDay);
		}
		if(consumetime!=null)
		{
			consumetime = new Date(consumetime.getTime()-EIGHT);
			Date nextDay = new Date(consumetime.getTime()+DAY);
			sql += " and consume_time between ? and ? ";
			params.add(consumetime);
			params.add(nextDay);
		}
		if(ordertime!=null)
		{
			ordertime = new Date(ordertime.getTime()-EIGHT);
			Date nextDay = new Date(ordertime.getTime()+DAY);
			sql += " and order_time between ? and ? ";
			params.add(ordertime);
			params.add(nextDay);
		}
		if(payway!=null && payway!=-1)
		{
			sql += " and pay_way=? ";
			params.add(payway);
		}
		if(price!=null)
		{
			sql += " and sale_price=? ";
			params.add(DoubleFormat.format(price));
		}
		if(zoneid!=null && zoneid!=-1)
		{
			sql += " and zone_id=? ";
			params.add(zoneid);
		}
		if(state!=null && state!=-1)
		{
			if(state == 6){
				sql += " and ((state = 0 and pay_way = 0) or state = 1)";
			} else {
				sql += " and state=?";
				params.add(state);
			}
		}
		
		logger.info("sql <" + sql + ">");
		List<Order> res = getTemplateMgr().getReadTp().query(sql, new RowMapper<Order>() {
			@Override
			public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
				Order order = new Order();
				order.setOrderid(rs.getLong("order_id"));
				order.setBooktime(rs.getDate("book_time"));
//				order.setConsumetime(rs.getDate("consume_time"));
				Timestamp ts = rs.getTimestamp("consume_time");
				if(ts != null)
					order.setConsumetime(new Date(ts.getTime()));
				//order.setOrdertime(rs.getDate("order_time"));
				ts = rs.getTimestamp("order_time");
				if(ts != null)
					order.setOrdertime(new Date(ts.getTime()));
				order.setPayway(rs.getInt("pay_way"));
				order.setPrice(rs.getDouble("sale_price"));
				order.setUserid(rs.getLong("user_id"));
				order.setZoneid(rs.getLong("zone_id"));
				order.setState(rs.getInt("state"));
				order.setUsername(rs.getString("user_name"));
				return order;
			}
			
		}, params.toArray());
		return res;
	}
	
	@Override
	public Map<Date, List<Order>> getUpOrderByQuery(Order order, Date dateF, Date dateT) {
		final Map<Date, List<Order>> orders = new HashMap<Date, List<Order>>();
		
		String sql = "select * from zaoke.`order_upgrade` where 1=1";

		Long userid = order.getUserid();
		Date booktime = order.getBooktime();
		Date consumetime = order.getConsumetime();
		Date ordertime = order.getOrdertime();
		Integer payway = order.getPayway();
		Double price = order.getPrice();
		Long zoneid = order.getZoneid();
		Integer state = order.getState();
		List<Object> params = new LinkedList<Object>();
		
		if(dateF != null && dateT != null){
			sql += " and book_time between ? and ? ";
			params.add(dateF);
			params.add(dateT);
		}
		
		if(userid!=null && userid > 0)
		{
			sql += " and user_id=? ";
			params.add(userid);
		}
		if(booktime!=null)
		{
			booktime = new Date(booktime.getTime()-EIGHT);
			Date nextDay = new Date(booktime.getTime()+DAY);
			sql += " and book_time between ? and ? ";
			params.add(booktime);
			params.add(nextDay);
		}
		if(consumetime!=null)
		{
			consumetime = new Date(consumetime.getTime()-EIGHT);
			Date nextDay = new Date(consumetime.getTime()+DAY);
			sql += " and consume_time between ? and ? ";
			params.add(consumetime);
			params.add(nextDay);
		}
		if(ordertime!=null)
		{
			ordertime = new Date(ordertime.getTime()-EIGHT);
			Date nextDay = new Date(ordertime.getTime()+DAY);
			sql += " and order_time between ? and ? ";
			params.add(ordertime);
			params.add(nextDay);
		}
		if(payway!=null && payway!=-1)
		{
			sql += " and pay_way=? ";
			params.add(payway);
		}
		if(price!=null)
		{
			sql += " and sale_price=? ";
			params.add(DoubleFormat.format(price));
		}
		if(zoneid!=null && zoneid!=-1)
		{
			sql += " and zone_id=? ";
			params.add(zoneid);
		}
		if(state!=null && state!=-1)
		{
			if(state == 6){
				sql += " and ((state = 0 and pay_way = 0) or state = 1)";
			} else {
				sql += " and state=?";
				params.add(state);
			}
		}
		
		logger.info("sql <" + sql + ">");
		getTemplateMgr().getReadTp().query(sql, new RowMapper<Order>() {
			@Override
			public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
				Order order = new Order();
				order.setOrderid(rs.getLong("order_id"));
				order.setBooktime(rs.getDate("book_time"));
				Timestamp ts = rs.getTimestamp("consume_time");
				if(ts != null)
					order.setConsumetime(new Date(ts.getTime()));
				ts = rs.getTimestamp("order_time");
				if(ts != null)
					order.setOrdertime(new Date(ts.getTime()));
				order.setPayway(rs.getInt("pay_way"));
				order.setPrice(rs.getDouble("sale_price"));
				order.setUserid(rs.getLong("user_id"));
				order.setZoneid(rs.getLong("zone_id"));
				order.setState(rs.getInt("state"));
				order.setUsername(rs.getString("user_name"));
				List<Order> ordersTmp = orders.get(order.getBooktime());
				if(ordersTmp == null){
					ordersTmp = new ArrayList<Order>();
					orders.put(order.getBooktime(), ordersTmp);
				}
				ordersTmp.add(order);
				return order;
			}
			
		}, params.toArray());
		return orders;
	}

	private static final String TABLE_FOOD = "`zaoke`.`order_food`";
	
	private static final String QUERY_VIEW_FOOD = "`order_id`, `user_id`, `food_id`, `food_name`, `food_type`, `sale_price`";

	public Map<Long, List<OrderFood>> getOrderFoodByOrderIds(Set<Long> orderids) {
		String sql = "select " + QUERY_VIEW_FOOD + " from " + TABLE_FOOD + " where `order_id` in (";
		sql += StringUtils.join(orderids, ",");
		sql += ")";
		
		final Map<Long, List<OrderFood>> foods = new HashMap<Long, List<OrderFood>>();
		getTemplateMgr().getReadTp().query(sql, new RowMapper<Integer>(){

			public Integer mapRow(ResultSet rs, int rowNum) throws SQLException {
				OrderFood food = new OrderFood();
				food.setOrderid(rs.getLong("order_id"));
				food.setUserid(rs.getLong("user_id"));
				food.setFoodid(rs.getLong("food_id"));
				food.setFoodname(rs.getString("food_name"));
				food.setFoodtype(rs.getInt("food_type"));
				food.setPrice(rs.getDouble("sale_price"));
				
				List<OrderFood> tmp = foods.get(food.getOrderid());
				if(tmp == null){
					tmp = new ArrayList<OrderFood>();
					foods.put(food.getOrderid(), tmp);
				}
				tmp.add(food);
				
				return rowNum;
			}
			
		});
		
		return foods;
	}

	class OrderRowMapper implements RowMapper<Order>{

		public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
			Order order = new Order();
			order.setOrderid(rs.getLong("order_id"));
			order.setUserid(rs.getLong("user_id"));
			order.setUsername(rs.getString("user_name"));
			order.setZoneid(rs.getLong("zone_id"));
			order.setPrice(rs.getDouble("sale_price"));
			order.setPayway(rs.getInt("pay_way"));
			order.setState(rs.getInt("state"));
			Timestamp t = rs.getTimestamp("order_time");
			if(t != null)
				order.setOrdertime(new Date(t.getTime()));
			order.setBooktime(rs.getDate("book_time"));
			order.setConsumetime(rs.getDate("consume_time"));
			return order;
		}
	}

	private static final String TABLE = "`zaoke`.`order`";
	
	private static final String QUERY_VIEW = "`order_id`, `user_id`, `user_name`, `zone_id`, `sale_price`, `pay_way`, `state`, `order_time`, `book_time`, `consume_time`";
	
	@Override
	public List<Order> getActiveOrdersByZoneId(long zoneId) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE + " where `zone_id` = ? and `state` in (?, ?)";
		return getTemplateMgr().getReadTp().query(sql, new OrderRowMapper(), new Object[]{zoneId, Order.STATE_PAID, Order.STATE_UNPAID});
	}
	
	public Order getOrderById(long orderid) {
		String sql = "select " + QUERY_VIEW + " from " + TABLE + " where `order_id` = ? ";
		List<Order> orders = getTemplateMgr().getReadTp().query(sql, new OrderRowMapper(), new Object[]{orderid});
		
		if(orders != null && orders.size() > 0)
			return orders.get(0);
		
		return null;
	}
	
	public boolean updateOrderState(int state, Order order){
		String sql = "update " + TABLE + " set state = ?, consume_time = ? where order_id = ? and state = ?";
		return getTemplateMgr().getWriteTp().update(sql, new Object[]{state, order.getConsumetime(), order.getOrderid(), order.getState()}) > 0;
	}

	@Override
	public void updateOrderStates() {
		String sql = "update  zaoke.`order` set state = 3 where state in (1) and book_time < now();";
		int count = getTemplateMgr().getWriteTp().update(sql);
		logger.info("count <" + count + "> for sql <" + sql + ">");
		sql = "update  zaoke.`order` set state = 4 where state in (0) and book_time < now();";
		count = getTemplateMgr().getWriteTp().update(sql);
		logger.info("count <" + count + "> for sql <" + sql + ">");
	}

	private static final String TABLE_ID = "`zaoke`.`order_id_gen`";

	public long genTodayId(final String date, final long zoneId) {
		final String sql = "insert into " + TABLE_ID + " (`date`, `zone_id`, `today_id`) select ?, " + zoneId + ", ifnull(max(today_id),0) + 1 from " + TABLE_ID + " where date= ? and zone_id = ?";
		KeyHolder holder = new GeneratedKeyHolder();
		int count =  getTemplateMgr().getWriteTp().getJdbcOperations().update(new PreparedStatementCreator() {
			
			public PreparedStatement createPreparedStatement(Connection con)
					throws SQLException {
				PreparedStatement ps = con.prepareStatement(sql);
				ps.setObject(1, date);
				ps.setObject(2, date);
				ps.setObject(3, zoneId);
				return ps;
			}
		}, holder);
		
		if(count > 0 && holder.getKey() != null){
			long key = holder.getKey().longValue();
			String sql1 = "select today_id from " + TABLE_ID + " where gen_id = " + key;
			return  getTemplateMgr().getReadTp().queryForLong(sql1);
		}
		
		return 0;
	}

	public boolean addOrders(final List<Order> orders) {
		String sql = "insert into `zaoke`.`order_upgrade`(`order_id`, `user_id`, `user_name`, `zone_id`, `sale_price`, `pay_way`, `state`, `order_time`, `book_time`, `consume_time`) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		
		getTemplateMgr().getWriteTp().getJdbcOperations().batchUpdate(sql, new BatchPreparedStatementSetter() {
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				Order order = orders.get(i);
				ps.setObject(1, order.getOrderid());
				ps.setObject(2, order.getUserid());
				ps.setObject(3, order.getUsername());
				ps.setObject(4, order.getZoneid());
				ps.setObject(5, order.getPrice());
				ps.setObject(6, order.getPayway());
				ps.setObject(7, order.getState());
				ps.setObject(8, order.getOrdertime());
				ps.setObject(9, order.getBooktime());
				ps.setObject(10, order.getConsumetime());
			}
			
			public int getBatchSize() {
				return orders.size();
			}
		});
		return true;
	}

	public boolean addFoods(final List<OrderFood> foods) {
		String sql = "insert into" + TABLE_FOOD + "(`order_id`, `user_id`, `food_id`, `food_name`, `food_type`, `sale_price`) values(?, ?, ?, ?, ?, ?)";
		
		getTemplateMgr().getWriteTp().getJdbcOperations().batchUpdate(sql, new BatchPreparedStatementSetter() {
			
			public void setValues(PreparedStatement ps, int i) throws SQLException {
				OrderFood food = foods.get(i);
				ps.setObject(1, food.getOrderid());
				ps.setObject(2, food.getUserid());
				ps.setObject(3, food.getFoodid());
				ps.setObject(4, food.getFoodname());
				ps.setObject(5, food.getFoodtype());
				ps.setObject(6, food.getPrice());
			}
			
			public int getBatchSize() {
				return foods.size();
			}
		});
		return true;
	}
}
